Gacha Normal Box

🗃️ Normal Gacha Box Setup

This is how to step-by-step create, and customize a Normal Gacha Box.

🧠 What is a Normal Gacha Box?

A Normal Gacha Box differs from the other types by just being an average Gacha box, you pick an entry then roll for a prize no gimmicks.

🌐 Database Tables:

Table Name Purpose
gacha_shop Stores Gacha UI info and general properties.
gacha_entries Defines pulls (single/multi) and prize probabilities.
gacha_items Links prizes to actual items players receive.

🏗️ Part 1: Creating the Gacha Shop (gacha_shop)

To start off we must define the basics of our Gacha box.

Example SQL:

INSERT INTO gacha_shop (
    min_gr, min_hr, name, url_banner,
    url_feature, url_thumbnail,
    wide, recommended, gacha_type, hidden
)
VALUES (
    0, 0, 'Butter Box',
    'http://PUBLICIP:8090/butterboxbanner.png',
    'http://PUBLICIP:8090/butterboxthumb.png',
    'http://PUBLICIP:8090/butterboxban.png',
    true, true, 0, false
);
INSERT INTO gacha_shop (
    min_gr, min_hr, name, url_banner,
    url_feature, url_thumbnail,
    wide, recommended, gacha_type, hidden
)
VALUES (
    0, 0, 'Butter Box',
    'http://PUBLICIP:8090/banner.png',
    'http://PUBLICIP:8090/feature.png',
    'http://PUBLICIP:8090/thumbnail.png',
    true, true, 0, false
);

📝 VALUES:

🔍 In-Game Preview:

0cdd61515000123933f34ff8313ace12.png 8bfab1e0c0bf74e13f2f92f38c831fd2.png

🏗️ Part 2: Defining Pull Types (gacha_entries)

Next up we have to define the pulls from which the players will choose from:

Single Pull (rolls 1 item):

INSERT INTO gacha_entries (
    gacha_id, entry_type, item_type, item_number,
    item_quantity, weight, rarity, rolls,
    daily_limit, frontier_points
) VALUES (
    (SELECT id FROM gacha_shop ORDER BY id DESC LIMIT 1),
    0, 19, 1, 0, 0, 0, 1, 0, 0
);
INSERT INTO gacha_entries (
    gacha_id, entry_type, item_type, item_number,
    item_quantity, weight, rarity, rolls,
    daily_limit, frontier_points
) VALUES (
    (SELECT id FROM gacha_shop ORDER BY id DESC LIMIT 1), 0, 19, 1, 0, 0, 0, 1, 0, 0
);

Multi Pull (rolls 10 items):

INSERT INTO gacha_entries (
    gacha_id, entry_type, item_type, item_number,
    item_quantity, weight, rarity, rolls,
    daily_limit, frontier_points
) VALUES (
    (SELECT id FROM gacha_shop ORDER BY id DESC LIMIT 1),
    1, 19, 10, 0, 0, 0, 10, 0, 0
);
INSERT INTO gacha_entries (
    gacha_id, entry_type, item_type, item_number,
    item_quantity, weight, rarity, rolls,
    daily_limit, frontier_points
) VALUES (
    (SELECT id FROM gacha_shop ORDER BY id DESC LIMIT 1), 1, 19, 10, 0, 0, 0, 10, 0, 0
);

📝 VALUES:

🏗️ Part 3: Adding Prizes

Every prize needs two insertions:

📖 Example: Adding “Iron Ore” (20 units, 25% chance)

  1. Define probability (weight): 
INSERT INTO gacha_entries (
    gacha_id, entry_type, item_type, item_number, item_quantity,
    weight, rarity, rolls, daily_limit, frontier_points
) VALUES (
    (SELECT id FROM gacha_shop ORDER BY id DESC LIMIT 1),
    100, 0, 0, 0, 25, 25, 0, 0, 0
);
INSERT INTO gacha_entries (
    gacha_id, entry_type, item_type, item_number, item_quantity,
    weight, rarity, rolls, daily_limit, frontier_points
) VALUES (
    (SELECT id FROM gacha_shop ORDER BY id DESC LIMIT 1), 100, 0, 0, 0, 25, 25, 0, 0, 0
);
  1. Link actual item: 
INSERT INTO gacha_items (
    entry_id, item_type, item_id, quantity
) VALUES (
    (SELECT id FROM gacha_entries ORDER BY id DESC LIMIT 1),
    7, 216, 20
);
INSERT INTO gacha_items (
    entry_id, item_type, item_id, quantity
) VALUES (
    (SELECT id FROM gacha_entries ORDER BY id DESC LIMIT 1), 7, 216, 20
);

🔧 Explanation of Prize Fields:

To add more prizes just repeat these two steps under “Part 3”.

📊 Part 4: Setting up probabilities (Optional)

Always ensure total weights = 100:

Prize Weight (probability %)
Iron Ore 25%
Whetstone 25%
Steak 20%
Mega Potion 15%
Max Potion 10%
Sprout Ore 5%
Total: 100% ✅

✨ Congrats on setting up your first Gacha

Then re-insert your Gacha sql.

🚩 EXTRA ~Cronjob or schedule this SQL to cycle Gacha recommended or wide banner~

BEGIN;
WITH current AS (
    SELECT id FROM gacha_shop 
    WHERE recommended = true AND gacha_type = 0 
    ORDER BY id ASC LIMIT 1
),
next AS (
    SELECT id FROM gacha_shop 
    WHERE gacha_type = 0 
    AND id > (SELECT id FROM current) 
    ORDER BY id ASC LIMIT 1
),
first AS (
    SELECT id FROM gacha_shop 
    WHERE gacha_type = 0 
    ORDER BY id ASC LIMIT 1
),
count_recommended AS (
    SELECT COUNT(*) AS cnt FROM gacha_shop WHERE recommended = true AND gacha_type = 0
)
UPDATE gacha_shop
SET 
    recommended = CASE 
        -- If a currently recommended exists, disable it
        WHEN id = (SELECT id FROM current) THEN false
        -- Enable the next one, or loop back to the first if none exist
        WHEN id = COALESCE((SELECT id FROM next), (SELECT id FROM first)) THEN true
        -- If none are recommended, enable the first one as a fallback
        WHEN (SELECT cnt FROM count_recommended) = 0 AND id = (SELECT id FROM first) THEN true
        ELSE recommended
    END,
    wide = CASE 
        WHEN id = (SELECT id FROM current) THEN false
        WHEN id = COALESCE((SELECT id FROM next), (SELECT id FROM first)) THEN true
        WHEN (SELECT cnt FROM count_recommended) = 0 AND id = (SELECT id FROM first) THEN true
        ELSE wide
    END
WHERE gacha_type = 0;
COMMIT;
BEGIN;
WITH current AS (
    SELECT id FROM gacha_shop 
    WHERE recommended = true AND gacha_type = 0 
    ORDER BY id ASC LIMIT 1
),
next AS (
    SELECT id FROM gacha_shop 
    WHERE gacha_type = 0 
    AND id > (SELECT id FROM current) 
    ORDER BY id ASC LIMIT 1
),
first AS (
    SELECT id FROM gacha_shop 
    WHERE gacha_type = 0 
    ORDER BY id ASC LIMIT 1
),
count_recommended AS (
    SELECT COUNT(*) AS cnt FROM gacha_shop WHERE recommended = true AND gacha_type = 0
)
UPDATE gacha_shop
SET 
    recommended = CASE 
        -- If a currently recommended exists, disable it
        WHEN id = (SELECT id FROM current) THEN false
        -- Enable the next one, or loop back to the first if none exist
        WHEN id = COALESCE((SELECT id FROM next), (SELECT id FROM first)) THEN true
        -- If none are recommended, enable the first one as a fallback
        WHEN (SELECT cnt FROM count_recommended) = 0 AND id = (SELECT id FROM first) THEN true
        ELSE recommended
    END,
    wide = CASE 
        WHEN id = (SELECT id FROM current) THEN false
        WHEN id = COALESCE((SELECT id FROM next), (SELECT id FROM first)) THEN true
        WHEN (SELECT cnt FROM count_recommended) = 0 AND id = (SELECT id FROM first) THEN true
        ELSE wide
    END
WHERE gacha_type = 0;
COMMIT;